from typing import List

from app.services.business.gaolu_diagnose.ind.abstract_ind import AbstractInd


class CG_LT_GL_GL04_Vshoude(AbstractInd):

    def get_name(self):
        return "CG_LT_GL_GL04_Vshoude"

    def get_data_source(self):
        return "mysql"

    def get_sql(self, params) -> list:
        sql_tieliang = """    
                    SELECT
                    SUM(tieliang) as  tieliang
                    FROM(
                    SELECT
                    t1.fnumber,
                    t1.fdate,
                    t2.tieliang
                    FROM
                    (SELECT
                     cg.syn_tq_rgaoluchutie.fnumber,
                     cg.syn_tq_rgaoluchutie.fdate
                    FROM  cg.syn_tq_rgaoluchutie  where fdate =DATE_SUB(CURDATE(), INTERVAL 1 DAY))t1
                    JOIN 
                     (SELECT
                    fnumber,
                    SUM(fquantity) as tieliang
                    FROM cg.syn_tq_rtieguan where CAST(fdatetime as date) >DATE_SUB(CURDATE(), INTERVAL 2 DAY)  
                    GROUP BY fnumber)t2
                    on t1.fnumber = t2.fnumber)t3
                    GROUP BY fdate desc limit 1
                """.format(**params)

        sql_kuanghao = """
               SELECT
                 syn_tq_mbom1.fdate,
                SUM(syn_tq_mbom1.fqty) as kuanghao
                FROM
                syn_tq_mbom1
                INNER JOIN syn_tq_mmain ON syn_tq_mbom1.fitemid = syn_tq_mmain.fitemid

                 where  syn_tq_mmain.ftypeb =1  and syn_tq_mbom1.finsid=8  and   syn_tq_mbom1.fdate =DATE_SUB(CURDATE(), INTERVAL 1 DAY) 

                group BY  syn_tq_mbom1.fdate DESC limit 1
                """.format(**params)

        sql_ore_V2O5 = """
                SELECT
                round(AVG(CG_LT_GL_GL04_Yuanranliao_Ore_V2O5),3) as V2O5,
                 CAST(GMT_CREATE AS DATE) AS DATE
                 FROM ironman_prod.cg_lt_gl04_hanfan_predict
                where  CG_LT_GL_GL04_Yuanranliao_Ore_V2O5>0  and  CG_LT_GL_GL04_Yuanranliao_Ore_V2O5<1  AND   CAST(GMT_CREATE AS DATE) = DATE_SUB(CURDATE(), INTERVAL 1.2 DAY)
                GROUP BY CAST(GMT_CREATE AS DATE) 
                """.format(**params)

        sql_Tie_V = """      
                select round(avg(jyh.jyjg),3) as CG_LT_GL_GL04_Tie_V from 
                 (select jyh.*,jyt.ph 
                from syn_zl_zl_jyh as jyh ,syn_zl_zl_jyt as jyt , syn_zl_zl_tqpd as tqpd 
                 where  jyh.jcxmbm='01048' and jyh.zfbz='0' and tqpd.ztbz ='1'
                and jyt.wlbm = '60110101' 
               and SUBSTRING(jyt.ph, 3, 1) = '4' 
               and  jyt.jytid = jyh.jytid 
               and  jyt.ph = tqpd.ph
                and jyh.jyjg NOT LIKE '%#%' 
               and  tqpd.tjsj > DATE_SUB(CURDATE(), INTERVAL 1.2 day)) as jyh
               """

        return [sql_tieliang, sql_kuanghao, sql_ore_V2O5, sql_Tie_V]

    @AbstractInd.cache_wrapper
    def get_result(self):
        sql_list: List[str] = self.get_sql(self.params)
        cursor = self.mysql_cursor
        #####字段1
        tieliang = sql_list[0]
        cursor.execute(tieliang)
        tieliang = cursor.fetchone()
        tieliang = tieliang['tieliang'] if tieliang['tieliang'] is not None else 0
        #####字段2
        kuanghao = sql_list[1]
        cursor.execute(kuanghao)
        kuanghao = cursor.fetchone()
        kuanghao = kuanghao['kuanghao'] if kuanghao['kuanghao'] is not None else 0

        #####字段4
        V2O5 = sql_list[2]
        conn, local_cursor = self.mysql_client.connect(db='ironman')
        local_cursor.execute(V2O5)
        V2O5 = local_cursor.fetchone()
        V2O5 = V2O5['V2O5'] if V2O5['V2O5'] is not None else 0
        self.mysql_client.connect_close(conn, local_cursor)
        #####字段3
        Tie_V = sql_list[3]
        cursor.execute(Tie_V)
        Tie_V = cursor.fetchone()
        Tie_V = Tie_V['CG_LT_GL_GL04_Tie_V'] if Tie_V['CG_LT_GL_GL04_Tie_V'] is not None else 0

        if V2O5 == 0 or kuanghao == 0: return 0

        # shoude = round(float(tieliang) * float(Tie_V) * 182 * 100 / float(kuanghao) / float(V2O5) / 102, 2)

        # 20230329 钒收得率调整计算逻辑调整
        v_shoude = round(Tie_V * 100/0.9359/ V2O5, 2)
        if v_shoude >= 100: return round(v_shoude * 0.81, 2)
        if 95 <= v_shoude < 100: return round(v_shoude * 0.87, 2)
        if 90 <= v_shoude < 95: return round(v_shoude * 0.91, 2)
        if 85 <= v_shoude < 90: return round(v_shoude * 0.95, 2)

        return v_shoude
